package day13;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import day11.JDBCUtil;

public class StudentManager {
	//作为全局变量，让所有方法都可以使用
	private static ArrayList<Student> list = new ArrayList<Student>();
	private static Scanner scanner = new Scanner(System.in);

	public static void main(String[] args) {
		// 从数据库中查询所有的学生信息
		list = selectAll();
		
		while (true) {
			System.out.println("1、打印所有学生");
			System.out.println("2、添加学生");
			System.out.println("3、删除学生");
			System.out.println("4、查找学生");
			System.out.println("5、修改学生");
			System.out.println("0、退出系统");
			int type = scanner.nextInt();
			if (type == 0) {
				System.out.println("退出管理系统");
				break;
			}
			
			switch (type) {
			case 1:// 打印所有学生
				printAllStudent();
				break;
			case 2:
				insertStudent();
				break;
			case 3:
				deleteStudent();
				break;
			case 4:
				updateStudent();
				break;
			default:
				break;
			}
		}
	}
	
	private static void updateStudent() {
		System.out.println("请输入要更新的学生id");
		int id = scanner.nextInt();
		System.out.println("请输入要更新的名字：");
		String name = scanner.next();
		System.out.println("请输入要更新的年龄：");
		int age = scanner.nextInt();
		System.out.println("请输入要更新的性别：");
		String gender = scanner.next();
		
		// 根据id更新只会更新唯一的一条
		String sql = "update student set name=?,age=?,gender=? where id=?";
		// 根据名字可能会更新多条
		// String sql1 = "update student set name=?,age=?,gender=? where name=?";
		
		list = selectAll();
	}

	private static void deleteStudent() {
		
		list = selectAll();
	}

	// 添加学生时候不需要输入id，因为id是数据库管理，自增
	private static void insertStudent() {
		System.out.println("请输入学生的名字");
		String name = scanner.next();
		System.out.println("请输入年龄");
		int age = scanner.nextInt();
		System.out.println("请输入性别");
		String gender = scanner.next();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(`name`,age,gender) VALUES(?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, age);
			preparedStatement.setString(3, gender);
			int count = preparedStatement.executeUpdate();
			if (count == 1) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		// 数据已经插入到数据库里面，但是打印的ArrayList<Student> list数据是没有添加
		// 刚才插入的Student的数据，这里处理方式有两种：
		// 1、jdbc在自增条件下插入数据，是可以返回那个自增的id的
		// 2、重新去查询一下，虽然这样有些麻烦，但是已经封住成一个方法，可以直接调用
		// list.add(student);
		// 在后面的web开发中，做了添加、修改、删除这些操作后需要重新查询一下数据
		// 需要和数据库数据保持一致
		list = selectAll();
	}

	private static void printAllStudent() {
		for (Student student : list) {
			System.out.println(student);
		}
	}
	
	public static ArrayList<Student> selectAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Student> list = new ArrayList<Student>();
		// 加载驱动
		try { // DBUtils
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,gender from student";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			// 判断下一行有没有，有返回true，并指向这一行，没有返回false
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}
	
}
